﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace LightSwitchApplication
{
    public class SQLAccess
    {
        private static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MMC_Content"].ConnectionString;

        public static DataTable GetNearestPlaces(decimal Longitude, decimal Latitude, int? MaxDistance, int? MaxCount, decimal? MaxPrice, int? MaxHours, int? MaxMinutes, DateTime? Date, bool? Occupied)
        {
            //List<int> IdList = new List<int>();
            DataTable procData = new DataTable("FoundPlaces");
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmdGetData = new SqlCommand();
                    cmdGetData.Connection = conn;
                    cmdGetData.CommandType = CommandType.StoredProcedure;
                    cmdGetData.CommandTimeout = 600;

                    #region Load data

                    cmdGetData.CommandText = "[dbo].[mmc_GetNearestPlaces]";
                    cmdGetData.Parameters.Add("@UserName", SqlDbType.NVarChar, 256);
                    cmdGetData.Parameters[0].Value = (Application.Current != null && Application.Current.User != null ? Application.Current.User.Name : "TestUser"); //Application.Current.User.Name;

                    cmdGetData.Parameters.Add("@Date", SqlDbType.DateTime);
                    if (Date.HasValue)
                    {
                        cmdGetData.Parameters[1].Value = Date.Value;
                    }
                    cmdGetData.Parameters.Add("@Longitude", SqlDbType.Decimal, 22);
                    cmdGetData.Parameters[2].Precision = 22;
                    cmdGetData.Parameters[2].Scale = 15;
                    cmdGetData.Parameters[2].Value = Longitude;

                    cmdGetData.Parameters.Add("@Latitude", SqlDbType.Decimal, 22);
                    cmdGetData.Parameters[3].Precision = 22;
                    cmdGetData.Parameters[3].Scale = 15;
                    cmdGetData.Parameters[3].Value = Latitude;

                    cmdGetData.Parameters.Add("@MaxDistance", SqlDbType.Int);
                    if (MaxDistance.HasValue)
                    {
                        cmdGetData.Parameters[4].Value = MaxDistance.Value;
                    }
                    cmdGetData.Parameters.Add("@MaxCount", SqlDbType.SmallInt);
                    if (MaxDistance.HasValue)
                    {
                        cmdGetData.Parameters[5].Value = MaxCount.Value;
                    }
                    cmdGetData.Parameters.Add("@MaxPrice", SqlDbType.Money);
                    if (MaxPrice.HasValue)
                    {
                        cmdGetData.Parameters[6].Value = MaxPrice.Value;
                    }
                    cmdGetData.Parameters.Add("@MaxHours", SqlDbType.TinyInt);
                    if (MaxHours.HasValue)
                    {
                        cmdGetData.Parameters[7].Value = MaxHours.Value;
                    }
                    cmdGetData.Parameters.Add("@MaxMinutes", SqlDbType.TinyInt);
                    if (MaxMinutes.HasValue)
                    {
                        cmdGetData.Parameters[8].Value = MaxMinutes.Value;
                    }
                    cmdGetData.Parameters.Add("@Occupied", SqlDbType.Bit);
                    if (Occupied.HasValue)
                    {
                        cmdGetData.Parameters[9].Value = Occupied.Value;
                    }
                    procData.Load(cmdGetData.ExecuteReader());

                    #endregion
                }
                catch (SqlException ex)// ex
                {
                    throw ex;
                    //LogError(ex);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed) conn.Close();
                }
            }
            return procData;
        }
    }
}